/**
 * 读取Excel内容
 */
package com.cnfangmao.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


public class ExcelUtil {
	private final static String EXCEL_FILESUFFIX_XLS="xls";  //xls文件名后缀
	private final static String EXCEL_FILESUFFIX_XLSX="xlsx"; //xlsx文件名后缀
	private static Logger logger = Logger.getLogger(ExcelUtil.class); 

	public static class ResultType {
		public final static int SUCCESS=0;  //成功
		
		public final static int EXCEL_RESULTTYPE=1000;
		public final static int EXCEL_IMPORT_SUCCESS=EXCEL_RESULTTYPE;  //待导入的EXCEL文件成功
		public final static int EXCEL_IMPORT_SUFFIX_ERROR=EXCEL_RESULTTYPE+1;  //待导入的EXCEL文件名不对
		public final static int EXCEL_IMPORT_UNKNOWN_ERROR=EXCEL_RESULTTYPE+2;  //导入EXCEL出现未知错误
		public final static int EXCEL_IMPORT_DATATYPE_ERROR=EXCEL_RESULTTYPE+3;  //导入EXCEL出现数据类型未知错误
		public final static int EXCEL_IMPORT_NOSHEET=EXCEL_RESULTTYPE+4;  //导入EXCEL文件没有表格
		public final static int EXCEL_IMPORT_TABLE_HEAD_ERROR=EXCEL_RESULTTYPE+5;  //表头出错
		public final static int EXCEL_IMPORT_UNZIP_ERROR=EXCEL_RESULTTYPE+6;  //解压缩文件出错
		
		
	}

	
	public final static class ExcelResult
	{
		public int m_nResult=ResultType.EXCEL_IMPORT_SUCCESS;
		public String m_strResult="";  
	}
	
	/****
	 * 是否为excel文件
	 * @param strPath
	 * @return
	 */
	public static boolean isExcelFile(String strPath)
	{
		boolean bResult=false;
		
		String strFileSuffix=FileUtil.getFileSuffix(strPath);
		if (strFileSuffix.equalsIgnoreCase(EXCEL_FILESUFFIX_XLS) || strFileSuffix.equalsIgnoreCase(EXCEL_FILESUFFIX_XLSX))
		{
			bResult=true;
		}
		
		return bResult;
	}
	/****
	 * 读取Excel内容
	 * @param strPath
	 * @param lstRow
	 * @return
	 */
	public  static ExcelResult ReadExcel(String strPath,String strFirstRow,List<String> lstRow)
	{
		ExcelResult result=new ExcelResult();
		result.m_nResult=ResultType.SUCCESS;
		
		String strFileSuffix=FileUtil.getFileSuffix(strPath);
		if (strFileSuffix.equalsIgnoreCase(EXCEL_FILESUFFIX_XLS))
		{//xls文件
			result=ReadXLSFor1stSheet(strPath,strFirstRow,lstRow);
		}
		else if (strFileSuffix.equalsIgnoreCase(EXCEL_FILESUFFIX_XLSX))
		{//xlsx文件
			result=ReadXlSXFor1stSheet(strPath,strFirstRow,lstRow);
		}
		else
		{//其它文件，错误
			result.m_nResult=ResultType.EXCEL_IMPORT_SUFFIX_ERROR;
			result.m_strResult="文件["+strPath+"]的后缀名必须为"+EXCEL_FILESUFFIX_XLS+"或"+EXCEL_FILESUFFIX_XLSX;
		}
		return result;
	}
	
	/****
	 * 从xls文件中读取内容
	 * @param strPath
	 * @param strFirstRow
	 * @param lstRow
	 * @return
	 */
	private static ExcelResult  ReadXLSFor1stSheet(String strPath,String strFirstRow,List<String> lstRow)
	{
		ExcelResult result=new ExcelResult();

		InputStream is=null;
		HSSFWorkbook hssfWorkbook =null;
		boolean bFirst=true;
		String strRow="";
		int nColumns=strFirstRow.split("\\|").length;
		String strName=FileUtil.getFileName(strPath);
		
		try
		{

			is=new FileInputStream(strPath);
			hssfWorkbook = new HSSFWorkbook(is);
			HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(0);
			
			if (hssfSheet!=null)
			{
				for(int i=hssfSheet.getFirstRowNum();i<=hssfSheet.getLastRowNum();i++)
				{
					 HSSFRow hssfRow = hssfSheet.getRow(i);
					 strRow="";
					 
					 if (hssfRow==null)
					 {
						 continue;
					 }
					 
					 String strDelimiter="|";

					 for(int j=hssfRow.getFirstCellNum();j<=hssfRow.getLastCellNum();j++)
					 {
						 HSSFCell cell = hssfRow.getCell(j);

						 if (cell==null)
						 {
							 continue;
						 }
						 
						 if (!bFirst)
						 {
							 strDelimiter="|hjl|"+(i+1)+"##"+(j+1)+"##";
						 }
						 
						 switch (cell.getCellType())
				         {
				                case HSSFCell.CELL_TYPE_NUMERIC: // 数字
				                    if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式  
				                        SimpleDateFormat sdf = null;  
				                        if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {  
				                            sdf = new SimpleDateFormat("HH:mm");  
				                        } else {// 日期  
				                            sdf = new SimpleDateFormat("yyyy-MM-dd");  
				                        }  
				                        Date date = cell.getDateCellValue();  
				                        strRow=strRow+strDelimiter+ sdf.format(date);  
				                    } else if (cell.getCellStyle().getDataFormat() == 58) {  
				                        // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)  
				                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");  
				                        double value = cell.getNumericCellValue();  
				                        Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);  
				                        strRow=strRow+strDelimiter+ sdf.format(date);  
				                    } else {  
				                        double value = cell.getNumericCellValue();  
				                        CellStyle style = cell.getCellStyle();  
				                        DecimalFormat format = new DecimalFormat();  
				                        String temp = style.getDataFormatString();  
				                        // 单元格设置成常规  
				                        if (temp.equals("General")) {  
				                            format.applyPattern("#");  
				                        }  
				                        strRow=strRow+strDelimiter+ format.format(value);  
				                    }  
			                	
				                    break;
				                case HSSFCell.CELL_TYPE_STRING: // 字符串
				                	strRow=strRow+strDelimiter+cell.getStringCellValue().trim();
				                    break;
				                case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
				                    strRow=strRow+strDelimiter+cell.getBooleanCellValue();
				                    break;
				                case HSSFCell.CELL_TYPE_FORMULA: // 公式
				                	strRow=strRow+strDelimiter+cell.getCellFormula();
				                    break;
				                case HSSFCell.CELL_TYPE_BLANK: // 空值
				                	strRow=strRow+strDelimiter+"hjlnovalue";
				                    break;
				                case HSSFCell.CELL_TYPE_ERROR: // 故障
				                default:
				        			result.m_nResult=ResultType.EXCEL_IMPORT_DATATYPE_ERROR;
				        			result.m_strResult="文件["+strName+"]第["+i+","+j+"]表格数据类型未知";
				                	logger.error(result.m_strResult);
				                    break;
				          }
						 
						 if (result.m_nResult!=ResultType.EXCEL_IMPORT_SUCCESS)
						 {
							 return result;
						 }
					 }
					 
					 if (bFirst)
					 {
						 bFirst=false;
						 strRow=strRow.substring(1);
						 if (!strRow.equalsIgnoreCase(strFirstRow))
						 {
			        			result.m_nResult=ResultType.EXCEL_IMPORT_TABLE_HEAD_ERROR;
			        			result.m_strResult="文件["+strName+"]首行表格数据["+strRow+"]与设置不对!["+strFirstRow+"]";
			                	logger.error(result.m_strResult);
			                	return result;
						 }
					 }
					 else
					 {
						 int nRowColumns=strRow.split("\\|hjl\\|").length;
						 if (nRowColumns-1==nColumns)
						 {//列相等
							 strRow=strRow.substring(5);
							 lstRow.add(strRow);
						 }
					 }
				}
			}
			else
			{
    			result.m_nResult=ResultType.EXCEL_IMPORT_NOSHEET;
    			result.m_strResult="文件["+strName+"]没有数据表格";
            	logger.error(result.m_strResult);
			}
		}
		catch(Exception e)
		{
			result.m_nResult=ResultType.EXCEL_IMPORT_UNKNOWN_ERROR;
			result.m_strResult="导入EXCEL文件出错!["+strName+"]["+e.getMessage()+"]";
			logger.error(result.m_strResult);
			e.printStackTrace();
		}
		finally
		{
			try {
				if (is!=null)
				{
					is.close();
				}
				if (hssfWorkbook!=null)
				{
					hssfWorkbook.close();
				}
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return result;
	}
	
	/****
	 * 从xlsx文件中读取内容
	 * @param strPath
	 * @param strFirstRow
	 * @param lstRow
	 * @return
	 */
	private static ExcelResult  ReadXlSXFor1stSheet(String strPath,String strFirstRow,List<String> lstRow)
	{
		ExcelResult result=new ExcelResult();

		XSSFWorkbook xssfWorkbook =null;
		boolean bFirst=true;
		String strRow="";
		int nColumns=strFirstRow.split("\\|").length;
		String strName=FileUtil.getFileName(strPath);

		
		try
		{
			//File file=new File("e:\\国药控股山西长治有限公司.xlsx");
			File file=new File(strPath);
			xssfWorkbook = new XSSFWorkbook(file);
		    XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);
			
			if (xssfSheet!=null)
			{
				for(int i=xssfSheet.getFirstRowNum();i<=xssfSheet.getLastRowNum();i++)
				{
					 XSSFRow hssfRow = xssfSheet.getRow(i);
					 strRow="";
					 
					 if (hssfRow==null)
					 {
						 continue;
					 }
					 String strDelimiter="|";
					 
					 for(int j=hssfRow.getFirstCellNum();j<=hssfRow.getLastCellNum();j++)
					 {
						 XSSFCell cell = hssfRow.getCell(j);

						 if (cell==null)
						 {
							 continue;
						 }
						 if (!bFirst)
						 {
							 strDelimiter="|hjl|"+(i+1)+"##"+(j+1)+"##";
						 }
						 
						 switch (cell.getCellType())
				         {
				                case HSSFCell.CELL_TYPE_NUMERIC: // 数字
				                    if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式  
				                        SimpleDateFormat sdf = null;  
				                        if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {  
				                            sdf = new SimpleDateFormat("HH:mm");  
				                        } else {// 日期  
				                            sdf = new SimpleDateFormat("yyyy-MM-dd");  
				                        }  
				                        Date date = cell.getDateCellValue();  
				                        strRow=strRow+strDelimiter+ sdf.format(date);  
				                    } else if (cell.getCellStyle().getDataFormat() == 58) {  
				                        // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)  
				                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");  
				                        double value = cell.getNumericCellValue();  
				                        Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);  
				                        strRow=strRow+strDelimiter+ sdf.format(date);  
				                    } else {  
				                        double value = cell.getNumericCellValue();  
				                        CellStyle style = cell.getCellStyle();  
				                        DecimalFormat format = new DecimalFormat();  
				                        String temp = style.getDataFormatString();  
				                        // 单元格设置成常规  
				                        if (temp.equals("General")) {  
				                            format.applyPattern("#");  
				                        }  
				                        strRow=strRow+strDelimiter+ format.format(value);  
				                    } 
				                    break;
				                case HSSFCell.CELL_TYPE_STRING: // 字符串
				                	strRow=strRow+strDelimiter+cell.getStringCellValue().trim();
				                    break;
				                case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
				                    strRow=strRow+strDelimiter+cell.getBooleanCellValue();
				                    break;
				                case HSSFCell.CELL_TYPE_FORMULA: // 公式
				                	strRow=strRow+strDelimiter+cell.getCellFormula();
				                    break;
				                case HSSFCell.CELL_TYPE_BLANK: // 空值
				                	strRow=strRow+strDelimiter+"hjlnovalue";
				                    break;
				                case HSSFCell.CELL_TYPE_ERROR: // 故障
				                default:
				        			result.m_nResult=ResultType.EXCEL_IMPORT_DATATYPE_ERROR;
				        			result.m_strResult="文件["+strName+"]第["+i+","+j+"]表格数据类型未知";
				                	logger.error(result.m_strResult);
				                    break;
				          }
						 
						 if (result.m_nResult!=ResultType.EXCEL_IMPORT_SUCCESS)
						 {
							 return result;
						 }
					 }
					 if (bFirst)
					 {
						 bFirst=false;
						 strRow=strRow.substring(1);
						 if (!strRow.equalsIgnoreCase(strFirstRow))
						 {
			        			result.m_nResult=ResultType.EXCEL_IMPORT_TABLE_HEAD_ERROR;
			        			result.m_strResult="文件["+strName+"]首行表格数据["+strRow+"]与设置不对!["+strFirstRow+"]";
			                	logger.error(result.m_strResult);
			                	return result;
						 }
					 }
					 else
					 {
						 int nRowColumns=strRow.split("\\|hjl\\|").length;
						 if (nRowColumns-1==nColumns)
						 {//列相等
							 strRow=strRow.substring(5);
							 lstRow.add(strRow);
						 }
					 }
				}
			}
			else
			{
    			result.m_nResult=ResultType.EXCEL_IMPORT_NOSHEET;
    			result.m_strResult="文件["+strName+"]没有数据表格";
            	logger.error(result.m_strResult);
			}
		}
		catch(Exception e)
		{
			result.m_nResult=ResultType.EXCEL_IMPORT_UNKNOWN_ERROR;
			result.m_strResult="导入EXCEL文件出错!["+strName+"]["+e.getMessage()+"]";
			logger.error(result.m_strResult);
			e.printStackTrace();
		}
		finally
		{
			try {
				xssfWorkbook.close();
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return result;
	}
}
